import pandas as pd

def merge_product_service(table1_path, table2_path, output_path):
    # 读取两个表格
    df1 = pd.read_excel(table1_path)
    df2 = pd.read_excel(table2_path)

    # 重命名df2中的列名，假设原来的'0'列名应为'company_name'
    df2.rename(columns={'0': 'company_name', '2': 'Product_Service'}, inplace=True)

    print("原始表格内容:")
    print(f"Table 1:\n{df1}")
    print(f"Table 2:\n{df2}")

    # 确保两个表格都有 'Company_Name' 列
    if 'company_name' not in df1.columns or 'company_name' not in df2.columns:
        raise ValueError("Both tables must have a 'Company_Name' column.")

    # 确保表格2中有 'Product_Service' 列
    if 'Product_Service' not in df2.columns:
        raise ValueError("Table 2 must have a 'Product_Service' column.")

    # 合并表格
    merged_df = pd.merge(df1, df2[['company_name', 'Product_Service']], on='company_name', how='left')

    print("\n合并后的表格内容:")
    print(merged_df)

    # 将合并后的表格保存到输出文件
    merged_df.to_excel(output_path, index=False)
    print(f"\n合并后的表格已保存到 {output_path}")

if __name__ == "__main__":
    table1_path = './data/file1.xlsx'
    table2_path = './data/file2.xlsx'
    output_path = 'merged_table1.xlsx'
    merge_product_service(table1_path, table2_path, output_path)
